//---------------------------------------------------------------------------------
//DBProcBean:数据库操作类
//---------------------------------------------------------------------------------
package com.util;
import java.sql.*;

import org.apache.commons.lang.StringUtils;

public class DBProcBean{
	  
  private String strDrive="";                                 //默认驱动程序
  private String strConn="";                                  //默认连接串
  private String strDbtype="";                                  //默认连接串

  private Connection Conn;
  private Statement Stmt;

  private int ResultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;    //默认游标类型
  private int ResultSetConcur = ResultSet.CONCUR_READ_ONLY;         //默认Type类型: ReadOnly

  public long allRecoder=0;                                     //所有记录行数
  public long PageSize;                                       //页数
  
  //------------------------------------------------------
  // DBProcBean
  // 
  // 
  //------------------------------------------------------
  public DBProcBean(){
    Stmt = null;
    Conn = null;
    strDrive = ConfigConts.CNS_DRIVER;
    strConn = ConfigConts.CNS_CONN;
    strDbtype = ConfigConts.CNS_DBTYPE;    
  }

  public DBProcBean(String setDriver,String setConn){
    Stmt = null;
    Conn = null;
    strDrive = setDriver;
    strConn = setConn;
  }
  //------------------------------------------------------
  // Connect
  // 
  // 
  //------------------------------------------------------
  public boolean Connect() throws CommException{//建立链接
    try{  
      Class.forName(strDrive).newInstance();
      Conn = DriverManager.getConnection(strConn,ConfigConts.CNS_USER,ConfigConts.CNS_PSTWD);
      return true;
    }catch(Exception e){
      throw new CommException(e.getMessage());
    }//try
  }//end Connect

  public Connection getConn() throws CommException{
    if (!fchkCnt()){
      throw new CommException("数据库未连接");
    }//if (!fchkCnt())
    return Conn;
  }
  //------------------------------------------------------
  // getRecord
  // 提取SQL,intType为可选参数,1代表设定的类型(默认),0为默认打开类型(JDBC默认打开类型)
  // 参数:  strSQL --
  //        Type -- 
  //        size -- 
  //        NowPage -- 
  //------------------------------------------------------
  public ResultSet getRecord(String strSQL,String Type,int size,int NowPage) throws CommException{
    if (!fchkCnt()){
        throw new CommException("数据库未连接");
    }//if (!fchkCnt())
    try {
      if (Type.trim().equals("1")){
        Stmt = Conn.createStatement(ResultSetType,ResultSetConcur);
      }else{
        Stmt = Conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      }//if
    }catch(Exception e){
        throw new CommException(e.getMessage());
    }//try
    ResultSet Rstemp2 =null;
    try {
      Rstemp2 = Stmt.executeQuery(strSQL);
      if (Rstemp2.next()) {
        //获取记录总数
        Rstemp2.last();
        allRecoder = Rstemp2.getRow();
        //定位当前页
        if (NowPage == 1 || allRecoder == 0){
          Rstemp2.beforeFirst();
        }else{
          int intTemp;
          intTemp = (NowPage-1)*size;
          if ( intTemp < allRecoder ){
            Rstemp2.absolute(intTemp);
          }else{
            Rstemp2.beforeFirst();
          }//if (intTemp<allRecoder)
        }// if (NowPage==1 || allRecoder =0)
      }else{
        allRecoder = 0;
        return Rstemp2;
      }//if (Rstemp2.next())
    }catch(Exception e){
      String ErrStr = e.getMessage();
      try{
        Stmt.close();
      }catch(Exception stmtEcp){
        ErrStr += "/n" + stmtEcp.getMessage();
      }//try
      throw new CommException(ErrStr);
    }finally{
      return Rstemp2;
    }//try    
  }//End getRecord
  //重载
  public ResultSet getRecord(String strSQL) throws CommException{
    return getRecord(strSQL,"0",1,1);
  }// getRecord重载结束
  //重载
  public ResultSet getRecord(String strSQL,int size) throws CommException{
    return getRecord(strSQL,"0",size,1);
  }// getRecord重载结束
  //重载
  public ResultSet getRecord(String strSQL,int size,int NowPage) throws CommException{
    return getRecord(strSQL,"0",size,NowPage);
  }// getRecord重载结束
  //重载
  //指定类型
  public ResultSet getRecord(String strSQL,int setType,int setConcur,int size,int NowPage) throws CommException{
    ResultSetType = setType;
    ResultSetConcur = setConcur;
    return getRecord(strSQL,"1",size,NowPage);
  }// getRecord重载结束
  //指定类型
  public ResultSet getRecord(String strSQL,int setType,int setConcur,int size) throws CommException{
    ResultSetType = setType;
    ResultSetConcur = setConcur;
    return getRecord(strSQL,"1",size,1);
  }// getRecord重载结束

  //----------------------------------------------------------------------------------------
  //重载： getRecord -- 返回二维数组
  //参数: strTable: 查询的表
  //      strIndexField: 主列 （惟一标识的列）
  //      strOutField: 输出的列
  //      strWhere: Where条件
  //      strOder: 排序条件
  //----------------------------------------------------------------------------------------
  public String[][] getArryRecord(String strSQL) throws CommException{
    //生成二维数组
    String strArr[][];
    java.sql.ResultSet rstIndex = getRecord(strSQL);
    if (rstIndex == null){
      return null;
    }
    try{
      if (rstIndex.next()){
        ResultSetMetaData rsmdIndex = rstIndex.getMetaData();

        int allCount = rsmdIndex.getColumnCount();
        rstIndex.last();
        int allrow = rstIndex.getRow();
        strArr = new String[allrow][allCount];
        rstIndex.beforeFirst();
        int i=0;
        while (rstIndex.next()){
          for (int j=1;j<=allCount;j++)
            strArr[i][j-1] = StringUtils.isBlank(rstIndex.getString(j))?rstIndex.getString(j).trim():"";
          i++;
        }//while
      }else{
        throw new CommException("没有数据");
      }//if
      rstIndex.close();
    }catch(Exception e){
      throw new CommException(e.getMessage());
    }//try
    close();
    return strArr;
  }//getRecord

  //----------------------------------------------------------------------------------------
  //重载：基本表分页处理过程
  //参数: strTable: 查询的表
  //      strIndexField: 主列 （惟一标识的列）
  //      strOutField: 输出的列
  //      strWhere: Where条件
  //      strOder: 排序条件
  //----------------------------------------------------------------------------------------
  public ResultSet getRecord(String strTable,String strIndexField,String strOutField,
    String strWhere,String strOder,int intPageSize,int intPage){

    String strWhereIndex;
    String sql;

    strWhere = strWhere.trim();

    if (!strWhere.equals("")){
      strWhereIndex = " Where " + strWhere + " and ";
      strWhere = "Where " + strWhere;
    }else{
      strWhereIndex = " Where "+strWhere;
    }

    sql = "select top "+ intPageSize +" "+ strTable +"."+ strOutField +",allRed from "+ strTable +
         ",(select count(*) as "+ "allRed from tb_news "+ strWhere +") A " + strWhereIndex 
         + strIndexField +" not in (select top "+ (intPageSize *(intPage-1)) +" "+
         strIndexField + " from "+ strTable +" "+ strWhere + strOder +") "+ strOder;
    return getRecord(sql,0);//只读，不可删
  }//getRecord

  //------------------------------------------------------
  // fchkCnt
  // 检查是否连接
  // 有连接返回True， 无连接执行返回false
  //------------------------------------------------------
  public boolean fchkCnt(){
    if (Conn==null){
      if (Connect()){
        return true;
      }else{
        return false;
      }
    }
    else{
      return true;
    }
  }//fchkCnt
  
  //------------------------------------------------------
  // frunSQL
  // 执行SQL
  // 成功执行返回True， 不成功执行返回false
  //------------------------------------------------------
  public boolean frunSQL(String strSQL) throws CommException{
    if (!fchkCnt()){                    //判断连接
      throw new CommException("数据库未连接");
    }
    //执行SQL
    try {
      Statement stmtTemp = Conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
      stmtTemp.executeUpdate(strSQL);
    }catch(Exception e){
      close();
      throw new CommException(e.getMessage());
    }
    close();
    return true;
  }//frunSQL


  //--------------------------------------------------------------
  // getValue
  // 提取单值 
  // 参数: strTable: 表名; strColumt: 返回列名; strWhere :Where条件
  //--------------------------------------------------------------
  public String getValue(String strTable,
		  String strColumtSet,String strColumt,String strWhere) throws CommException{
    if (!fchkCnt()){
        throw new CommException("数据库未连接");
    }
    if (strTable == "" || strColumt == ""){
        throw new CommException("查询条件为空");
    }
    ResultSet rs = null;
    try {
      if (!strWhere.trim().equalsIgnoreCase("")){
        //strWhere = strWhere.replaceAll("%20","");
        strWhere = "where "+ strWhere;
      }
      String strSQL = "Select "+ strColumtSet +" from "+ strTable +" "+ strWhere;
      rs = getRecord(strSQL);
      if (!rs.next()) {
        throw new CommException("无此记录");
      }
      String strTemp = rs.getString(strColumt);
      rs.close();
      rs = null;
      return strTemp;
    }catch(Exception e){
      throw new CommException(e.getMessage());
    }finally{
      try {
        if (rs != null) rs.close();
      }catch(Exception e){
          throw new CommException(e.getMessage());
      }      
    }//try
  }//End getValue

  //--------------------------------------------------------------
  // getValue
  // 提取单值 
  // 参数: strTable: 表名; strColumt: 返回列名; strWhere :Where条件
  //--------------------------------------------------------------
  public String getValue(String strSQL) throws CommException{
    if (!fchkCnt()){
        throw new CommException("数据库未连接");
    }
    ResultSet rs = null;
    try {
      rs = getRecord(strSQL);
      if (!rs.next()) {
          throw new CommException("无此记录");
      }
      String strTemp = rs.getString(1);
      rs.close();
      rs = null;
      return strTemp;
    }catch(Exception e){
        throw new CommException(e.getMessage());
    }finally{
      try {
        if (rs != null) rs.close();
      }catch(Exception e){
          throw new CommException(e.getMessage());
      }      
    }//try
  }//End getValue

  //--------------------------------------------------------------
  // chkRecord
  // 提取单值 
  // 参数: strTable: 表名; strColumt: 返回列名; strWhere :Where条件
  //--------------------------------------------------------------
  public String getValue(String strTable,String strColumt,String strWhere){
    return getValue(strTable,strColumt,strColumt,strWhere);
  }//End getValue

  //------------------------------------------------------
  // chkRecord
  // 判断SQL是否存在
  // 存在还返回True，不存在返回false
  //------------------------------------------------------
  public boolean chkRecord(String strSQL) throws CommException{
    if (!fchkCnt()){
        throw new CommException("数据库未连接");
    }

    if (StringUtils.isEmpty(strSQL)){
        throw new CommException("无此记录");
    }
    ResultSet rs = null;
    try {
      rs = getRecord(strSQL);
      if (!rs.next()) {
        return false;
      }else{
        return true;
      }
    }catch(Exception e){
        throw new CommException(e.getMessage());
    }finally{
      try {
        if (rs != null) rs.close();
      }catch(Exception e){
          throw new CommException(e.getMessage());
      }
    }//try
  }//End chkRecord

  //--------------------------------------------------------------
  // getColumn
  // 提取单值 
  // 参数: strTable: 表名; strColumt: 返回列名; strWhere :Where条件
  //--------------------------------------------------------------
  public String getColumn(String strPrtID){
    return getValue("SELECT column_content FROM tb_column LEFT JOIN tb_admin_menu ON "+
        "tb_column.column_title = tb_admin_menu.menu_id WHERE tb_admin_menu.menu_title = '"+
        strPrtID +"' and menu_isactive = 1 and column_isactive = 1");
  }//End getColumn


  //------------------------------------------------------
  // close
  // 关闭
  //------------------------------------------------------
  public void close() throws CommException {
    try {
      if (Stmt!=null) Stmt.close();
      if (Conn!=null) Conn.close();

      Stmt = null;
      Conn = null;
    }
    catch(Exception e) {
      e.printStackTrace();
      throw new CommException(e.getMessage());
    } 
  }

/**
 * @return the strDbtype
 */
public String getStrDbtype() {
	return strDbtype;
}

}//class